package com.music.mapper.music;

import com.music.dto.music.PlaylistDTO;
import com.music.dto.music.SongDTO;
import com.music.dto.tag.TagDTO;
import com.music.entity.music.PlaylistEntity;
import org.apache.ibatis.annotations.*;

import java.util.List;

public interface PlaylistMapper {
    /*
     * @Description 根据用户Id列出歌单
     * @Author DY
     * @Date 2021/11/23
     **/
    @Select({"<script>SELECT playlist_id, playlist_name, tag_id, playlist_info, image_url, " +
            "image_name, is_public, gmt_create FROM music_playlist WHERE user_id = #{userId} " +
            "<if test='playlistName != null'> AND playlist_name LIKE CONCAT('%', #{playlistName}, '%') </if>" +
            "LIMIT #{start}, #{length} " +
            "</script>"})
    List<PlaylistDTO> listPlaylistByUserId(
            @Param("start") Integer start,
            @Param("length") Integer length,
            @Param("userId") Long userId,
            @Param("playlistName") String playlistName) throws Exception;

    /*
     * @Description 根据用户Id查询歌单总数
     * @Author DY
     * @Date 2021/11/23
     **/
    @Select({"<script>SELECT COUNT(*) FROM music_playlist WHERE user_id = #{userId} " +
            "<if test='playlistName != null'> AND playlist_name LIKE CONCAT('%', #{playlistName}, '%') </if>" +
            "</script>"})
    Long countPlaylistByUserId(
            @Param("userId") Long userId,
            @Param("playlistName") String playlistName) throws Exception;

    /*
     * @Description 新增歌单
     * @Author DY
     * @Date 2021/11/23
     **/
    @Insert("Insert into music_playlist ( " +
            "user_id, playlist_name, tag_id, playlist_info, image_url, image_name, is_public, gmt_create) " +
            "VALUES (#{playlistEntity.userId}, #{playlistEntity.playlistName}, #{playlistEntity.tagId}, " +
            "#{playlistEntity.playlistInfo}, #{playlistEntity.imageUrl}, #{playlistEntity.imageName}, " +
            "#{playlistEntity.isPublic}, #{playlistEntity.gmtCreate})")
    Integer addPlaylist(@Param("playlistEntity") PlaylistEntity playlistEntity) throws Exception;

    /*
     * @Description 更新歌单
     * @Author DY
     * @Date 2021/11/23
     **/
    @Update("Update music_playlist set playlist_name = #{playlistEntity.playlistName}, " +
            "tag_id = #{playlistEntity.tagId}, playlist_info = #{playlistEntity.playlistInfo}, " +
            "image_url = #{playlistEntity.imageUrl}, image_name = #{playlistEntity.imageName}, " +
            "is_public = #{playlistEntity.isPublic} " +
            "WHERE playlist_id = #{playlistEntity.playlistId}")
    Integer updatePlaylist(@Param("playlistEntity") PlaylistEntity playlistEntity) throws Exception;

    /*
     * @Description 删除歌单
     * @Author DY
     * @Date 2021/11/23
     **/
    @Delete("DELETE from music_playlist WHERE playlist_id = #{playlistId}")
    Integer removePlaylist(@Param("playlistId") Long playlistId) throws Exception;

    /*
     * @Description 查询所有子标签
     * @Author DY
     * @Date 2021/11/23
     **/
    @Select("SELECT tag_id, tag_name from basic_tag WHERE tag_type = 2 ")
    List<TagDTO> listAllTag() throws Exception;


    /*
     * @Description 根据用户Id列出音乐广场歌单
     * @Author hbh
     * @Date 2021/11/23
     **/
    @Select("<script>SELECT a.playlist_id, a.user_id, user_name, c.image_url avatar, playlist_name, tag_id, playlist_info, " +
            "a.image_url, a.image_name, is_public, a.gmt_create, COUNT(b.user_id) collectors, " +
            "(select count(*) FROM collection_playlist WHERE user_id = #{userId} AND playlist_id = a.playlist_id) " +
            "AS isCollect, " +
            "(select count(*) FROM music_song WHERE is_public = 1 AND playlist_id = a.playlist_id) " +
            "AS songCount, " +
            "(select count(*) FROM music_download md LEFT JOIN music_song ms " +
            "on md.song_id = ms.song_id WHERE playlist_id = a.playlist_id) " +
            "AS downloadCount " +
            "FROM music_playlist a LEFT JOIN collection_playlist b ON a.playlist_id = b.playlist_id " +
            "left join basic_user c on a.user_id = c.user_id " +
            "WHERE true " +
            "AND is_public = 1 " +
            "<if test='isSelf == 0'> AND  a.user_id != #{userId} </if>" +
            "<if test='isSelf == 1 || isSelf == 2'> AND  a.user_id = #{userId} </if>" +
            "<if test='playlistName != null'> AND playlist_name LIKE CONCAT('%', #{playlistName}, '%') </if>" +
            "GROUP BY a.playlist_id " +
            "<if test='sortId == -1'> ORDER BY collectors DESC </if>" +
            "<if test='sortId == -2'> ORDER BY songCount DESC </if>" +
            "<if test='sortId == -3'> ORDER BY downloadCount DESC </if>" +
            "LIMIT #{start}, #{length} " +
            "</script>")
    List<PlaylistDTO> listMusicSquareByUserId(
            @Param("isSelf") Integer isSelf,
            @Param("playlistName") String playlistName,
            @Param("sortId") Integer sortId,
            @Param("start") Integer start,
            @Param("length") Integer length,
            @Param("userId") Long userId) throws Exception;

    /*
     * @Description 根据用户Id查询音乐广场歌单总数
     * @Author hbh
     * @Date 2021/11/26
     **/
    @Select("<script>SELECT COUNT(*) FROM music_playlist WHERE true " +
            "AND is_public = 1 " +
            "<if test='isSelf == 0'> AND user_id != #{userId} </if>" +
            "<if test='isSelf == 1 || isSelf == 2'> AND user_id = #{userId} </if>" +
            "<if test='playlistName != null'> AND playlist_name LIKE CONCAT('%', #{playlistName}, '%') </if>" +
            "</script>")
    Long countMusicSquareByUserId(@Param("userId") Long userId,
                                  @Param("isSelf") Integer isSelf,
                                  @Param("playlistName") String playlistName) throws Exception;

    /*
     * @Description 根据歌单Id查询音频链接
     * @Author DY
     * @Date 2021/11/29
     **/
    @Select("SELECT audio_url from music_song where playlist_id = #{playlistId}")
    List<String> getAudioUrl(@Param("playlistId") Long playlistId) throws Exception;

    /*
     * @Description 根据歌单Id查询下载次数
     * @Author DY
     * @Date 2021/12/26
     **/
    @Select("SELECT COUNT(*) from music_song ms " +
            "RIGHT JOIN music_download md " +
            "on md.song_id = ms.song_id  " +
            "WHERE ms.playlist_id = #{playlistId} ")
    Integer getDownloadSum(@Param("playlistId") Long playlistId) throws Exception;

    /*
     * @Description 根据歌单Id查询下载次数
     * @Author DY
     * @Date 2021/12/26
     **/
    @Update("UPDATE music_playlist SET tag_id = #{tagIds} " +
            "WHERE playlist_id = #{playlistId} ")
    Integer updateTagByPlaylist(@Param("playlistId") Long playlistId,
                                @Param("tagIds") String tagIds) throws Exception;
}
